import urllib.request
import urllib.parse
import json
import pymysql

mysql_host = 'localhost'
mysql_db = 'school_list'
mysql_user = 'root'
mysql_password = 'swrxts123'
mysql_port = 3306
db = pymysql.connect(host=mysql_host, user=mysql_user,port=mysql_port, password=mysql_password, db=mysql_db,charset='utf8') # 连接数据库编码注意是utf8，不然中文结果输出会乱码

def mysql_create():
    url = "https://www.cingta.com/school/api/name_uni_list/"
    response = urllib.request.urlopen(url)
    html = response.read().decode('utf-8')
    # print(html)
    target = json.loads(html)['data']['list']
    # print(target)
    cursor = db.cursor()
    for i in range(0, len(target)):
        result = [(target[i]['lineid'], target[i]['code'], target[i]['schoolname'], target[i]['province'], target[i]['city'],target[i]['department'],
                   target[i]['level'], target[i]['type'], target[i]['link'])]
        print(result)
        sql_insert ="insert into school(lineid, code, schoolname, province, city, department, level, type, " \
                             "link) values(%s,%s,%s,%s,%s,%s,%s,%s,%s) "
        # sql_key="CREATE UNIQUE INDEX lineid ON DUMPLINGS(lineid)"

        # cursor.execute("DROP TABLE IF EXISTS DUMPLINGS")
        print('存储第' + str(i) + '条学校数据')
        cursor.executemany(sql_insert,result)# 执行SQL语句
        # cursor.execute(sql_key)
        db.commit() # 关闭数据库连

mysql_create()

